﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace MyTest
{
    /// <summary>
    /// 教员:薛海
    /// 微信:xuehai03
    /// QQ:289477003
    /// SQLite示例
    /// 2019-2-20
    /// </summary>
    public partial class frmSQLite : Form
    {
        public frmSQLite()
        {
            InitializeComponent();
        }

        const string connStr = @"data source=DB\MySchool.db";

        SQLiteConnection conn = new SQLiteConnection(connStr);


        DataSet ds;
        SQLiteDataAdapter adapter;

        //登录事件
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                conn.Open();

                string stuNo = this.txtUserName.Text.Trim();
                string pwd = this.txtPwd.Text.Trim();

                string sqlStr = string.Format(@"select count(*) from student
            where stuNo ='{0}' and stuName='{1}'", stuNo, pwd);

                SQLiteCommand comm = new SQLiteCommand(sqlStr, conn);

                int result = Convert.ToInt32(comm.ExecuteScalar());
                if (result > 0)
                {
                    MessageBox.Show("登录成功");
                }
                else
                {
                    MessageBox.Show("登录失败");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        private void frmSQLite_Load(object sender, EventArgs e)
        {
            getInfo();
        }

        public void getInfo()
        {
            try
            {
                ds = new DataSet();
                string sqlStr = "select * from student";
                adapter = new SQLiteDataAdapter(sqlStr, conn);
                adapter.Fill(ds, "student");
                this.dataGridView1.DataSource = ds.Tables["student"];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                conn.Open();
                string stuNo = this.txtUserName.Text.Trim();
                string stuName = this.txtPwd.Text.Trim();
                string sqlStr = string.Format("insert into student values('{0}','{1}')", stuNo, stuName);
                SQLiteCommand comm = new SQLiteCommand(sqlStr, conn);
                int result = comm.ExecuteNonQuery();
                if (result > 0)
                {
                    MessageBox.Show("新增成功");
                    getInfo();
                }
                else
                {
                    MessageBox.Show("新增失败");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        private void tsmiDel_Click(object sender, EventArgs e)
        {
            try
            {
                conn.Open();
                string stuNo = this.dataGridView1.SelectedRows[0].Cells["stuNo"].Value.ToString();
                string sqlStr = string.Format("delete from student where stuNo='{0}'", stuNo);
                SQLiteCommand comm = new SQLiteCommand(sqlStr, conn);
                int result = comm.ExecuteNonQuery();
                if (result > 0)
                {
                    MessageBox.Show("新增成功");
                    getInfo();
                }
                else
                {
                    MessageBox.Show("新增失败");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
